class Message < ActiveRecord::Base
  has_and_belongs_to_many :user_group
  belongs_to :page
  validates_presence_of(:title, :details, :expires_on)

  class << self
    def find_by_user(user)
      return find_public_messages unless user
      find_user_messages(user)
    end

    private
      def find_public_messages
        Message.find_by_sql <<-SQL
          select m.* from messages m where 
            expires_on >= '#{Time.now.to_date}' and
            m.id not in (select message_id from messages_user_groups)
            order by updated_at desc
        SQL
      end

      def find_user_messages(user)
        #Find messages associated to the user's group and include all public
        #messages
        Message.find_by_sql <<-SQL
          select * from (
            select m.* from messages m, messages_user_groups mugs where
              m.expires_on >= '#{Time.now.to_date}' and
              m.id = mugs.message_id and 
              mugs.user_group_id in
                (select user_group_id from user_groups_users where user_id = #{user.id}) 
            union
              select * from messages  where 
                expires_on >= '#{Time.now.to_date}' and
                id not in (select message_id from messages_user_groups)
          ) as messages order by updated_at desc
        SQL
      end
  end #end class
end
